from flask import jsonify, render_template, request

#
from blues.cloud import bp
from database import session_maker

# 项目维度- ECS综合查询
ecs_sql = """
SELECT
	x.org AS `组织`,
	x.project AS `项目`,
	x.`CPU最大使用率%`,
	x.`CPU最小使用率%`,
	x.`CPU平均使用率%`,
	x.`内存最大使用率%`,
	x.`内存最小使用率%`,
	x.`内存平均使用率%`,
	y.`磁盘使用率%`
FROM
	(
		SELECT
			org,
			project,
			ROUND(
				100 * sum(cpu_max_used) / sum(cpu),
				2
			) AS 'CPU最大使用率%',
			ROUND(
				100 * sum(cpu_min_used) / sum(cpu),
				2
			) AS 'CPU最小使用率%',
			ROUND(
				100 * SUM(cpu_avg_used) / sum(cpu),
				2
			) AS 'CPU平均使用率%',
			ROUND(
				100 * SUM(mem_max_used) / sum(memory),
				2
			) AS '内存最大使用率%',
			ROUND(
				100 * SUM(mem_min_used) / sum(memory),
				2
			) AS '内存最小使用率%',
			ROUND(
				100 * SUM(mem_avg_used) / sum(memory),
				2
			) AS '内存平均使用率%'
		FROM
			(
				SELECT
					a.org,
					a.project,
					a.instance_id,
					c.cpu,
					c.memory,
					ROUND(c.cpu * b.cpu_max / 100, 3) AS cpu_max_used,
					ROUND(c.cpu * b.cpu_min / 100, 3) AS cpu_min_used,
					ROUND(c.cpu * b.cpu_avg / 100, 3) AS cpu_avg_used,
					ROUND(c.memory * b.mem_max / 100, 3) AS mem_max_used,
					ROUND(c.memory * b.mem_min / 100, 3) AS mem_min_used,
					ROUND(c.memory * b.mem_avg / 100, 3) AS mem_avg_used
				FROM
					cmdb_cloud_resource a,
					cms_ecs b,
					c_ecs_info c
				WHERE
					a.instance_id = b.instance_id
				AND a.instance_id = c.instance_id
				AND a.is_deleted IS NULL
				AND a.res_type = 'ECS'
				AND b.date BETWEEN '{}'
				AND '{}'
				AND DATE_FORMAT(c.record_time, '%Y-%m-%d') = b.date
			) ecs_all
		GROUP BY
			org,
			project
	) x
LEFT JOIN (
	SELECT
		org,
		project,
		ROUND(
			SUM(disk_used_100) / SUM(disk_size),
			2
		) AS '磁盘使用率%'
	FROM
		(
			SELECT
				a.org,
				a.project,
				a.instance_id,
				b.disk_id,
				b.disk_size,
				b.disk_usage,
				b.disk_size * b.disk_usage AS disk_used_100
			FROM
				cmdb_cloud_resource a,
				cms_ecs_disk b
			WHERE
				a.instance_id = b.instance_id
			AND a.is_deleted IS NULL
			AND b.date = (SELECT MAX(date) FROM cms_ecs_disk WHERE date<='{}')
		) disk_all
	GROUP BY
		org,
		project
) y ON x.org = y.org
AND x.project = y.project;
"""

# 项目维度- RDS综合查询
rds_sql = """
SELECT
	x.org AS '组织',
	x.project AS '项目',
	ROUND(
		SUM(x.cpu_max_used_100) / SUM(x.cpu),
		2
	) AS 'CPU最大使用率%',
	ROUND(
		SUM(x.cpu_min_used_100) / SUM(x.cpu),
		2
	) AS 'CPU最小使用率%',
	ROUND(
		SUM(x.cpu_avg_used_100) / SUM(x.cpu),
		2
	) AS 'CPU平均使用率%',
	ROUND(
		SUM(x.mem_max_used_100) / SUM(x.mem),
		2
	) AS '内存最大使用率%',
	ROUND(
		SUM(x.mem_min_used_100) / SUM(x.mem),
		2
	) AS '内存最小使用率%',
	ROUND(
		SUM(x.mem_avg_used_100) / SUM(x.mem),
		2
	) AS '内存平均使用率%',
	ROUND(
		SUM(x.conn_max_used_100) / SUM(x.conn_max),
		2
	) AS '连接数最大使用率%',
	ROUND(
		SUM(x.conn_min_used_100) / SUM(x.conn_max),
		2
	) AS '连接数最小使用率%',
	ROUND(
		SUM(x.conn_avg_used_100) / SUM(x.conn_max),
		2
	) AS '连接数平均使用率%',
	ROUND(AVG(x.disk_max_used_100), 2) AS '磁盘平均使用率%'
FROM
	(
		SELECT
			a.org,
			a.project,
			a.instance_id,
			b.db_instance_class,
			c.cpu,
			c.mem,
			c.conn_max,
			d.date,
			c.cpu * d.cpu_max AS cpu_max_used_100,
			c.cpu * d.cpu_min AS cpu_min_used_100,
			c.cpu * d.cpu_avg AS cpu_avg_used_100,
			c.mem * d.mem_max AS mem_max_used_100,
			c.mem * d.mem_min AS mem_min_used_100,
			c.mem * d.mem_avg AS mem_avg_used_100,
			c.conn_max * d.con_max AS conn_max_used_100,
			c.conn_max * d.con_min AS conn_min_used_100,
			c.conn_max * d.con_avg AS conn_avg_used_100,
			d.disk_max AS disk_max_used_100
		FROM
			cmdb_cloud_resource a,
			c_rds_info b,
			(
				SELECT
					db_instance_class,
					cpu,
					mem,
					conn_max
				FROM
					class_rds
				GROUP BY
					db_instance_class,
					cpu,
					mem,
					conn_max
			) c,
			cms_rds d
		WHERE
			a.res_type = 'RDS'
		AND a.is_deleted IS NULL
		AND a.instance_id = b.db_instance_id
		AND a.instance_id = d.instance_id
		AND d.date BETWEEN '{}'
		AND '{}'
		AND b.db_instance_class = c.db_instance_class
	) x
GROUP BY
	x.org,
	x.project;
"""

# 项目维度- OSS综合查询
oss_sql = """
SELECT
	x.org AS '组织',
	x.project AS '项目',
	ROUND( 100 * SUM( x.size_in_bytes ) / SUM( x.capacity_in_bytes ), 2 ) AS 'OSS存储使用率%' 
FROM
	(
	SELECT
		a.org,
		a.project,
		a.instance_id,
		b.capacity,
	IF
		( b.capacity =- 1, 100, b.capacity ) * 1000000000 AS capacity_in_bytes,
		b.size_in_bytes 
	FROM
		cmdb_cloud_resource a,
		c_oss_info b 
	WHERE
		a.instance_id = b.bucket 
		AND ( a.is_deleted IS NULL OR a.is_deleted = 0 ) 
		AND DATE_FORMAT( b.record_time, '%Y-%m-%d' ) = ( SELECT DATE_FORMAT( MAX( record_time ), '%Y-%m-%d' ) FROM c_oss_info WHERE DATE_FORMAT( record_time, '%Y-%m-%d' ) <= '{}' ) # 取截止范围内最新的数据
	) x 
GROUP BY
	x.org,
	x.project;
"""

eip_sql = """
SELECT
	x.org as '组织',
	x.project as '项目',
	ROUND(
		100 * SUM(x.net_rx_max) / SUM(x.bandwidth_in_byte),
		2
	) AS '带宽最大使用率%',
	ROUND(
		100 * SUM(x.net_rx_min) / SUM(x.bandwidth_in_byte),
		2
	) AS '带宽最小使用率%',
	ROUND(
		100 * SUM(x.net_rx_avg) / SUM(x.bandwidth_in_byte),
		2
	) AS '带宽平均使用率%'
FROM
	(
		SELECT
			a.org,
			a.project,
			a.instance_id,
			b.net_rx_max,
			b.net_rx_min,
			b.net_rx_avg,
			b.bandwidth * 1000000 AS bandwidth_in_byte
		FROM
			cmdb_cloud_resource a,
			cms_eip b
		WHERE
			a.res_type = 'EIP'
		AND a.is_deleted IS NULL
		AND a.instance_id = b.instance_id
		AND b.date BETWEEN '{}'	AND '{}'
	) x
GROUP BY
	x.org,
	x.project;
"""

odps_sql = """
SELECT
	z.org AS '组织',
	z.project AS '项目',
	SUM( z.cpu_hour ) AS '消耗CPU核数合计(核*小时)',
	SUM( z.mem_hour ) AS '消耗内存合计(GB*小时)',
	SUM( z.total_task ) AS '合计任务数',
	SUM( z.disk_used_g ) AS '存储使用量(GB)',
	SUM( z.disk_quota_g ) AS '存储配额(GB)',
	ROUND( SUM( z.disk_used_g ) / SUM( z.disk_quota_g ) * 100, 2 ) AS '存储使用率%' 
FROM
	(
	SELECT
		y.org,
		y.project,
		x.* 
	FROM
		(
		SELECT
			a.project AS odps_project,
			ROUND( IFNULL( SUM( a.total_cost_cpu ), 0 ) / 100 / 3600, 0 ) AS cpu_hour,
			ROUND( IFNULL( SUM( a.total_cost_mem ), 0 ) / 1024 / 3600, 0 ) AS mem_hour,
			IFNULL( SUM( a.total_task_num ), 0 ) AS total_task,
			ROUND( b.data_filelogicallength / 1024 / 1024 / 1024, 2 ) AS disk_used_g,
			ROUND( b.project_quota_size / 1024 / 1024 / 1024 / 3, 0 ) AS disk_quota_g 
		FROM
			abm_saas_ag.odps_project_info_topn a,
			abm_saas_ag.region_odps_project_storage_info_realtime b 
		WHERE
			a.project = b.project 
			AND a.ds BETWEEN '{}' 
			AND '{}' 
		GROUP BY
			a.project 
		) x
		RIGHT JOIN ( SELECT org, project, instance_id FROM `op-admin`.cmdb_cloud_resource WHERE res_type = 'ODPS' AND is_deleted IS NULL ) y ON x.odps_project = y.instance_id 
	) z 
GROUP BY
	z.org,
	z.project;
"""


def ecs_project_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(ecs_sql.format(start, end, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'cpu_max_usage': row[2], 'cpu_min_usage': row[3],
                'cpu_avg_usage': row[4], 'mem_max_usage': row[5], 'mem_min_usage': row[6], 'mem_avg_usage': row[7],
                'disk_usage': row[8],
            }
            data_li.append(tmp_dict)
    return data_li


def rds_project_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(rds_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'cpu_max_usage': row[2], 'cpu_min_usage': row[3],
                'cpu_avg_usage': row[4], 'mem_max_usage': row[5], 'mem_min_usage': row[6], 'mem_avg_usage': row[7],
                'con_max_usage': row[8], 'con_min_usage': row[9], 'con_avg_usage': row[10], 'disk_usage': row[11]
            }
            data_li.append(tmp_dict)
    return data_li


def oss_project_stats() -> list:
    """
    获取oss最新状态，按项目统计
    :return:
    """
    data_li = []
    with session_maker() as session:
        rs = session.execute(oss_sql)  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'oss_use_percent': row[2]
            }
            data_li.append(tmp_dict)
    return data_li


def eip_project_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(eip_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'bandwidth_max_usage': row[2], 'bandwidth_min_usage': row[3],
                'bandwidth_avg_usage': row[4],
            }
            data_li.append(tmp_dict)
    return data_li


def odps_project_stats(start, end) -> list:
    data_li = []
    with session_maker() as session:
        rs = session.execute(odps_sql.format(start, end))  # res是获取的对象
        for row in rs:
            tmp_dict = {
                'org': row[0], 'project': row[1], 'cpu_core_hours': row[2], 'mem_gb_hours': row[3],
                'tasks': row[4], 'storage_used_size_gb': row[5], 'storage_quota_gb': row[6], 'storage_usage': row[7]
            }
            data_li.append(tmp_dict)
    return data_li


@bp.route('/api/v1/cloud/ecs_project_stats', methods=['GET'])
def ecs_project_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print("ecs project stats:", start, end)
    data_li = ecs_project_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/rds_project_stats', methods=['GET'])
def rds_project_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = rds_project_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/oss_project_stats', methods=['GET'])
def oss_project_stats_api():
    data_li = oss_project_stats()
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/eip_project_stats', methods=['GET'])
def eip_project_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    print(start, end)
    data_li = eip_project_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


@bp.route('/api/v1/cloud/odps_project_stats', methods=['GET'])
def odps_project_stats_api():
    start = request.args.get('start')  # 2023-09-01
    end = request.args.get('end')  # 2023-09-20
    # odps的日期传参需要删除多余的符号"-"
    start = start.replace("-", "")
    end = end.replace("-", "")
    print(start, end)
    data_li = odps_project_stats(start, end)
    return jsonify({"code": 200, "data": data_li})


# 渲染web页面
@bp.route('/cloud/project_stats')
def resource_project_stats_page():
    return render_template('resource_project_stats.html', active='cloud_project')
